package com.heh.mybatisplus.controller;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.*;

/**
 * @项目名： spring-emq
 * @包名： com.heh.mybatisplus.controller
 * @文件名: DatabaseToExcelExporter
 * @创建者: liufudong@szyh-smart.com
 * @创建时间: 2024/10/24 19:55
 * @描述：
 */
public class DatabaseToExcelExporter {
    // MySQL JDBC URL, Username, Password
    private static final String URL = "jdbc:mysql://localhost:3306/";
    private static final String USER = "root";
    private static final String PASSWORD = "szyh2016";
//edu_luohu

    public static void main(String[] args) {
        exportEachTableToExcel();
    }

    public static void exportEachTableToExcel() {
        // 查询所有表名
        String queryTables = "SELECT table_name FROM information_schema.tables WHERE table_schema = 'edu_luohu'";

        try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement preparedStatement = connection.prepareStatement(queryTables);
             ResultSet resultSet = preparedStatement.executeQuery()) {

            // 遍历所有表
            while (resultSet.next()) {
                String tableName = resultSet.getString("table_name");
                exportTableStructureToExcel(connection, tableName);
            }

            System.out.println("All tables exported successfully!");

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void exportTableStructureToExcel(Connection connection, String tableName) {
        // 查询表结构
        String query = "SELECT column_name, data_type, column_comment " +
                "FROM information_schema.columns WHERE table_schema = 'edu_luohu' AND table_name = ?";

        try (PreparedStatement preparedStatement = connection.prepareStatement(query)) {

            preparedStatement.setString(1, tableName);

            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                // 创建 Excel 工作簿
                Workbook workbook = new XSSFWorkbook();
                Sheet sheet = workbook.createSheet(tableName);

                // 创建标题行
                Row headerRow = sheet.createRow(0);
                String[] headers = {"名称", "类型", "注释"};
                for (int i = 0; i < headers.length; i++) {
                    Cell cell = headerRow.createCell(i);
                    cell.setCellValue(headers[i]);
                }

                // 填充表结构数据
                int rowIndex = 1;
                while (resultSet.next()) {
                    Row row = sheet.createRow(rowIndex++);
                    row.createCell(0).setCellValue(resultSet.getString("column_name"));  // 列名称
                    row.createCell(1).setCellValue(resultSet.getString("data_type"));    // 列类型
                    row.createCell(2).setCellValue(resultSet.getString("column_comment")); // 列注释
                }

                // 自动调整列宽
                for (int i = 0; i < headers.length; i++) {
                    sheet.autoSizeColumn(i);
                }

                // 将工作簿写入文件，以表名命名文件
                try (FileOutputStream fileOut = new FileOutputStream(tableName + "_structure.xlsx")) {
                    workbook.write(fileOut);
                    System.out.println("Excel file created for table: " + tableName);
                }

                workbook.close();
            }
        } catch (SQLException | IOException e) {
            e.printStackTrace();
        }
    }
}
